import oracledb
import psutil
import os
from tqdm import tqdm
from cuslog import CusLog
from runtimecalc import decorator


version_info = (0, 9, 9)
versionstr = '.'.join(map(str, version_info))

class DBFunction:
	def __init__(self, dbconfigdict):
		#logset
		self.dblog = CusLog(log_type='dbfunction')
		#dbset
		oracle_ip = dbconfigdict.get('oracle_ip')
		oracle_port = dbconfigdict.get('oracle_port')
		oracle_sid = dbconfigdict.get('oracle_sid')
		oracle_user = dbconfigdict.get('oracle_user')
		oracle_password = dbconfigdict.get('oracle_password')
		oracle_dbthickmode = dbconfigdict.get('dbthickmode', False)
		self.tqdmncols = dbconfigdict.get('tqdmncols', 100)
		self.bar_format = dbconfigdict.get('bar_format', '{l_bar}{bar}| {n_fmt}/{total_fmt} [{rate_fmt}]')
		try:
			if oracle_dbthickmode:
				oracledb.init_oracle_client()
				self.dblog.cusinfo("Oracle thick mode enabled for database connection.")
			self.connection = oracledb.connect('%s/%s@%s:%s/%s'%(oracle_user,oracle_password,oracle_ip,oracle_port,oracle_sid))
			self.cursor = self.connection.cursor()
			self.dblog.cusinfo("Database connection established successfully.")
		except Exception as e:
			self.dblog.cuserror(f"Error connecting to database: {e}")
			input("Press Enter to exit...")
			os._exit(0)
		#memory monitor set
		self.process = psutil.Process()
		#temp save vars
		self.currtradingday = '20231231'
	
	def look_memory_usage(self):
		self.memory_info = self.process.memory_info()
		memory_usage_mb = self.memory_info.rss / (1024 * 1024)
		self.dblog.cusinfo(f"Current memory usage: {memory_usage_mb:.2f} MB")
	
	def check_table_exists(self, tabname):
		checksql = f"select 1 from {tabname} t where rownum='1'"
		try:
			self.cursor.execute(checksql)
			self.dblog.cusinfo(f"Table {tabname} exists and is accessible.")
			return True
		except Exception as e:
			self.dblog.cuserror(f"Table {tabname} does not exist or cannot be accessed: {e}")
			return False
	
	def create_table(self, createsqlfile, tabname='ARCH_CM003_USEREVENT'):
		createsql = ''
		execlist = []
		print(f"Creating table {tabname} using script {createsqlfile}...")
		try:
			#get create table sql
			with open(createsqlfile, 'r', encoding='utf-8') as file:
				for currline in file:
					if currline.strip().startswith('--') or currline.strip() == '':
						continue
					else:
						createsql += currline
		except Exception as e:
			self.dblog.cuserror(f"Error reading create table script {createsqlfile}: {e}")
			input("Press Enter to exit...")
			os._exit(0)
		for currsql in createsql.split(';'):
			if currsql.strip() != '':
				if ('.' in tabname and 'create index' in currsql.lower()):
					currsql = currsql.replace('IDX_CM003_USEREVENT_1', f"IDX_{tabname.split('.')[1]}_1")
				currsql = currsql.replace('ARCH_CM003_USEREVENT', tabname)
				execlist.append(currsql.strip())
		try:
			for currsql in execlist:
				self.cursor.execute(currsql)
				self.connection.commit()
			self.dblog.cusinfo(f"Table created successfully using script {createsqlfile}.")
			input("Press Enter to continue...")
		except Exception as e:
			self.dblog.cuserror(f"Error creating table using script {createsqlfile}: {e}")
			input("Press Enter to exit...")
			os._exit(0)

	@decorator
	def get_usereventinfo(self, tradingday, tabname="t_s_brokeruserevent"):
		currdayeventinfo = []
		countsql = f"select count(1) from {tabname} t where t.tradingday='{tradingday}'"
		self.cursor.execute(countsql)
		rowcount = int(self.cursor.fetchone()[0])
		selectsql = f"select * from {tabname} t where t.tradingday='{tradingday}' order by eventsequenceno,eventtime"
		self.cursor.execute(selectsql)
		for currow in tqdm(self.cursor, total=rowcount, desc=f"Fetching events {tradingday}", unit="record", unit_scale=True, ncols=self.tqdmncols, leave=False, bar_format=self.bar_format):
			currdayeventinfo.append(currow)
		#currdayeventinfo = self.cursor.fetchall()
		self.dblog.cusinfo(f"Total {len(currdayeventinfo)} user event info fetched for trading day {tradingday}")
		self.look_memory_usage()
		self.currtradingday = tradingday
		return currdayeventinfo

	@decorator
	def get_authcodeinfo(self, tabname='t_authenticationcode'):
		directappdict = {}
		relayappdict = {}
		selectsql = f"select trim(USERPRODUCTINFO) USERPRODUCTINFO, nvl(APPTYPE, 1) APPTYPE from {tabname}"
		self.cursor.execute(selectsql)
		for currrow in self.cursor:
		# Process each row as needed
			(USERPRODUCTINFO, APPTYPE) = currrow
			if APPTYPE == "1":
				#directapplist.append(USERPRODUCTINFO)
				directappdict[USERPRODUCTINFO] = "1"
			elif APPTYPE == "2":
				#relayapplist.append(USERPRODUCTINFO)
				relayappdict[USERPRODUCTINFO] = "2"
			elif APPTYPE == "3":
				#relayapplist.append(USERPRODUCTINFO)
				relayappdict[USERPRODUCTINFO] = "3"
			else:
				self.dblog.cusinfo(f"Unknown APPTYPE {APPTYPE} for USERPRODUCTINFO {USERPRODUCTINFO}")
		self.dblog.cusinfo(f"Total {len(directappdict)} direct app and {len(relayappdict)} relay app info fetched")
		self.look_memory_usage()
		return directappdict, relayappdict
	
	@decorator
	def insert_event(self, eventdata, tabname='ARCH_CM003_BROKERUSEREVENT', operdate=None, rowscommit=5000):
		rowscount = 0
		if operdate is None:
			operdate = self.currtradingday
		delete_sql = f"delete from {tabname} where tradingday='{operdate}'"
		colnum = len(eventdata[0])
		insertsql = self.gen_insertsql(tabname, colnum)
		self.cursor.execute(delete_sql)
		self.connection.commit()
		for currrecord in tqdm(eventdata, desc=f"Inserting events {operdate}", unit="record", unit_scale=True, ncols=self.tqdmncols, leave=False, bar_format=self.bar_format):
			try:
				self.cursor.execute(insertsql, currrecord)
			except Exception as e:
				self.dblog.cuserror(f"Error inserting record {currrecord}: {e}")
			rowscount += 1
			if(rowscount % rowscommit == 0):
				self.connection.commit()
		self.connection.commit()
		self.dblog.cusinfo(f"Total {len(eventdata)} records inserted into table {tabname}")
	
	def gen_insertsql(self, tabname, colnum):
		insertsql = f"insert into {tabname} values("
		for i in range(1, colnum+1):
			if i == colnum:
				insertsql += f":{i})"
			else:
				insertsql += f":{i}, "
		return insertsql
	
	def close_connection(self):
		self.cursor.close()
		self.connection.close()
		self.dblog.cusinfo("Database connection closed.")
	
	def logger_close(self):
		self.dblog.clear_handlers()
	


if __name__ == '__main__':
	import sys
	import os
	if (len(sys.argv) > 1 and sys.argv[1] == '-v'):
		scriptname = os.path.basename(__file__)
		print(f"{scriptname}:{versionstr}")
		sys.exit(0)


	#test code
	import os
	confdict = {}
	confdict['oracle_ip'] = os.getenv('DEVDBIP', '127.0.0.1')
	confdict['oracle_port'] = os.getenv('DEVDBPORT', '1521')
	confdict['oracle_sid'] = os.getenv('DEVDBSID', 'xhdw')
	confdict['oracle_user'] = os.getenv('DEVDBUSER', '')
	confdict['oracle_password'] = os.getenv('DEVDBPASSWORD', 'test1234')
	DBinstance = DBFunction(confdict)
	DBinstance.get_usereventinfo('20250102', tabname="xhdw.base_s_brokeruserevent")